﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using MotoreDataBase.MotoreDbException;

namespace MotoreDataBase
{

    #region Classi Parametri
    public class Parameter
    {
        public string Name { get; set; }
        public object Value { get; set; }

        public Parameter(string Name, object Value)
        {
            this.Name = Name;
            this.Value = Value;
        }

    }

    public class ListParameters : List<Parameter>
    { }

    #endregion

    public class GestoreBase
    {
        public static string stringConnection = string.Empty;



        #region Get Region
        /// <summary>
        /// Metodo che restituisce una lista di oggetti
        /// </summary>
        /// <typeparam name="T">Tipo di oggetti restituito</typeparam>
        /// <returns></returns>
        public static List<T> GetList<T>()
            where T:new()
        {
            return GetList<T>(null);
        }

        public static List<T> GetList<T>(params Parameter[] parameters)
            where T : new()
        {
            string query = "";
            List<T> lista = new List<T>();
            Type tipo = typeof(T);
            var listAttriutes = tipo.GetCustomAttributes(typeof(Nome), false);
            if (listAttriutes.Length >= 1)
            {
                query = string.Format("SELECT * FROM {0} ", listAttriutes[0]);
                Dictionary<string, System.Reflection.PropertyInfo> proprieta = new Dictionary<string, System.Reflection.PropertyInfo>();
                foreach (var item in tipo.GetProperties())
                {
                    var attr = item.GetCustomAttributes(typeof(Nome), false);
                    if (attr.Length > 0)
                        proprieta.Add(attr[0].ToString(), item);
                }
                using (SqlConnection SqlConn = new SqlConnection(stringConnection))
                {
                    SqlConn.Open();
                    using (SqlCommand SqlCmd = SqlConn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        if (parameters != null && parameters.Length > 0)
                        {
                            query += " WHERE "; 
                            int i = 0;
                            foreach (var item in parameters)
                            {
                                SqlCmd.Parameters.Add(new SqlParameter(item.Name, item.Value));
                                query += string.Format("{0} = @{0}", item.Name);
                                if (i < parameters.Length - 1)
                                    query += " AND ";
                            }
                        }
                        using (SqlDataReader reader = SqlCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var t = new T();
                                foreach (var prop in proprieta)
                                {
                                    SetProperty<T>(t, reader, prop);
                                }
                                lista.Add(t);
                            }
                        }
                    }
                }
            }
            return lista;
        }

        /// <summary>
        /// Metodo che restituisce una lista di oggetti
        /// </summary>
        /// <typeparam name="T">Tipo di oggetti restituito</typeparam>
        /// <param name="selectProp">Array delle proprietà su cui fare la select</param>
        /// <returns></returns>
        public static List<T> GetList<T>(params object[] selectProp)
        where T:new()
        {
            string query = "";
            string selectElem = "";
            Dictionary<string, System.Reflection.PropertyInfo> proprieta = new Dictionary<string, System.Reflection.PropertyInfo>();
            Type tipo = typeof(T);

            for (int i = 0; i < selectProp.Length; i++)
            {
                var attrs = ((System.Reflection.PropertyInfo)selectProp[i]).GetCustomAttributes(typeof(Nome), false);
                if (attrs.Length > 0)
                {
                    selectElem += attrs[0].ToString();
                    proprieta.Add(attrs[0].ToString(), (System.Reflection.PropertyInfo)selectProp[i]);
                    if (i < selectProp.Length - 1)
                        selectElem += ',';
                }

            }
            List<T> lista = new List<T>();
            
            var listAttriutes = tipo.GetCustomAttributes(typeof(Nome), false);
            if (listAttriutes.Length >= 1)
            {
                query = string.Format("SELECT {0} FROM {1} ", selectElem, listAttriutes[0]);
                using (SqlConnection SqlConn = new SqlConnection(stringConnection))
                {
                    SqlConn.Open();
                    using (SqlCommand SqlCmd = SqlConn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        using (SqlDataReader reader = SqlCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var t = new T();
                                foreach (var prop in proprieta)
                                {
                                    SetProperty<T>(t, reader, prop);
                                }
                                lista.Add(t);
                            }
                        }
                    }
                }
            }
            return lista;
        }
        #endregion

        #region NonQuery Region
        /// <summary>
        /// Metodo che restituisce una lista di oggetti T utilizzando la query indicata in <paramref name="nomeQuery"/>
        /// </summary>
        /// <typeparam name="T">Tipo oggetto restituito</typeparam>
        /// <param name="nomeQuery">Nome della query definita nella classe</param>
        /// <param name="parameters">Lista di parametri da passare alla query</param>
        /// <returns></returns>
        public static List<T> ExecuteNonQueryToList<T>(string nomeQuery, params Parameter[] parameters)
            where T:new()
        {
            List<T> lista = new List<T>();
            Type tipo = typeof(T);
            var listAttriutes = tipo.GetCustomAttributes(typeof(NonQuery), false);
            string query = null;
            if (listAttriutes.Length >= 1)
            {
                foreach(var item in listAttriutes)
                {
                    if (((NonQuery)item).Nome == nomeQuery)
                        query = item.ToString();
                }
                if (string.IsNullOrEmpty(query))
                    return lista;
                Dictionary<string, System.Reflection.PropertyInfo> proprieta = new Dictionary<string, System.Reflection.PropertyInfo>();
                foreach (var item in tipo.GetProperties())
                {
                    var attr = item.GetCustomAttributes(typeof(Nome), false);
                    if (attr.Length > 0)
                        proprieta.Add(attr[0].ToString(), item);
                }
                using (SqlConnection SqlConn = new SqlConnection(stringConnection))
                {
                    SqlConn.Open();
                    using (SqlCommand SqlCmd = SqlConn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        if (parameters != null)
                        {
                            foreach (var item in parameters)
                            {
                                SqlCmd.Parameters.Add(new SqlParameter(item.Name, item.Value));
                            }
                        }
                        using (SqlDataReader reader = SqlCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var t = new T();
                                foreach (var prop in proprieta)
                                {
                                    SetProperty<T>(t, reader, prop);
                                }
                                lista.Add(t);
                            }
                        }
                    }
                }
            }
            return lista;
        }

        public static List<T> ExecuteNonQueryToList<T>(string nomeQuery)
            where T : new()
        {
            return ExecuteNonQueryToList<T>(nomeQuery, null);
        }

        /// <summary>
        /// Metodo che restituisce un oggetto T utilizzando la query indicata in <paramref name="nomeQuery"/>
        /// </summary>
        /// <typeparam name="T">Tipo oggetto restituito</typeparam>
        /// <param name="nomeQuery">Nome della query definita nella classe</param>
        /// <param name="parameters">Lista di parametri da passare alla query</param>
        /// <returns></returns>
        public static T ExecuteNonQueryToObject<T>(string nomeQuery, params Parameter[] parameters)
            where T : new()
        {
            T oggetto = new T();
            Type tipo = typeof(T);
            var listAttriutes = tipo.GetCustomAttributes(typeof(NonQuery), false);
            string query = null;
            if (listAttriutes.Length >= 1)
            {
                foreach (var item in listAttriutes)
                {
                    if (((NonQuery)item).Nome == nomeQuery)
                        query = item.ToString();
                }
                if (string.IsNullOrEmpty(query))
                    return oggetto;
                Dictionary<string, System.Reflection.PropertyInfo> proprieta = new Dictionary<string, System.Reflection.PropertyInfo>();
                foreach (var item in tipo.GetProperties())
                {
                    var attr = item.GetCustomAttributes(typeof(Nome), false);
                    if (attr.Length > 0)
                        proprieta.Add(attr[0].ToString(), item);
                }
                using (SqlConnection SqlConn = new SqlConnection(stringConnection))
                {
                    SqlConn.Open();
                    using (SqlCommand SqlCmd = SqlConn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        if (parameters != null)
                        {
                            foreach (var item in parameters)
                            {
                                SqlCmd.Parameters.Add(new SqlParameter(item.Name, item.Value));
                            }
                        }
                        using (SqlDataReader reader = SqlCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                foreach (var prop in proprieta)
                                {
                                    SetProperty<T>(oggetto, reader, prop);
                                }
                            }
                            return oggetto;
                        }
                    }
                }
            }
            throw new ItemNotFoundException(); 
        }

        public static T ExecuteNonQueryToObject<T>(string nomeQuery)
            where T : new()
        {
            return ExecuteNonQueryToObject<T>(nomeQuery, null);
        }
        /// <summary>
        /// Metodo che esegue la query indicata in <paramref name="nomeQuery"/> per query che modificano il database
        /// </summary>
        /// <typeparam name="T">True se tutto OK</typeparam>
        /// <param name="nomeQuery">Nome della query definita nella classe</param>
        /// <param name="parameters">Lista di parametri da passare alla query</param>
        /// <returns></returns>
        public static bool ExecuteNonQueryModifyDb<T>(string nomeQuery, params Parameter[] parameters)
            where T : new()
        {
            T oggetto = new T();
            Type tipo = typeof(T);
            var listAttriutes = tipo.GetCustomAttributes(typeof(NonQuery), false);
            string query = null;
            if (listAttriutes.Length >= 1)
            {
                foreach (var item in listAttriutes)
                {
                    if (((NonQuery)item).Nome == nomeQuery)
                        query = item.ToString();
                }
                if (string.IsNullOrEmpty(query))
                    return false;
                Dictionary<string, System.Reflection.PropertyInfo> proprieta = new Dictionary<string, System.Reflection.PropertyInfo>();
                foreach (var item in tipo.GetProperties())
                {
                    var attr = item.GetCustomAttributes(typeof(Nome), false);
                    if (attr.Length > 0)
                        proprieta.Add(attr[0].ToString(), item);
                }
                using (SqlConnection SqlConn = new SqlConnection(stringConnection))
                {
                    SqlConn.Open();
                    using (SqlCommand SqlCmd = SqlConn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        if (parameters != null)
                        {
                            foreach (var item in parameters)
                            {
                                SqlCmd.Parameters.Add(new SqlParameter(item.Name, item.Value));
                            }
                        }

                        if (SqlCmd.ExecuteNonQuery() > 0)
                            return true;
                    }
                }
            }
            throw new ExecuteNonQueryException();
        }

        public static bool ExecuteNonQueryModifyDb<T>(string nomeQuery)
            where T : new()
        {
            return ExecuteNonQueryModifyDb<T>(nomeQuery, null);
        }
        /// <summary>
        /// Metodo che restituisce una lista di stringhe utilizzando la query indicata in <paramref name="nomeQuery"/>
        /// </summary>
        /// <typeparam name="T">Tipo oggetto da cui prenderà la query</typeparam>
        /// <param name="nomeQuery">Nome della query definita nella classe</param>
        /// <param name="parameters">Lista di parametri da passare alla query</param>
        /// <returns></returns>
        public static List<string> ExecuteNonQueryToListOfString<T>(string nomeQuery, params Parameter[] parameters)
        {
            List<string> lista = new List<string>();
            Type tipo = typeof(T);
            var listAttriutes = tipo.GetCustomAttributes(typeof(NonQuery), false);
            string query = null;
            if (listAttriutes.Length >= 1)
            {
                foreach (var item in listAttriutes)
                {
                    if (((NonQuery)item).Nome == nomeQuery)
                        query = item.ToString();
                }
                if (string.IsNullOrEmpty(query))
                    return lista;
                using (SqlConnection SqlConn = new SqlConnection(stringConnection))
                {
                    SqlConn.Open();
                    using (SqlCommand SqlCmd = SqlConn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        if (parameters != null)
                        {
                            foreach (var item in parameters)
                            {
                                SqlCmd.Parameters.Add(new SqlParameter(item.Name, item.Value));
                            }
                        }
                        using (SqlDataReader reader = SqlCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                lista.Add(reader[0].ToString());
                            }
                        }
                    }
                }
            }
            return lista;
        }

        public static List<string> ExecuteNonQueryToListOfString<T>(string nomeQuery)
        {
            return ExecuteNonQueryToListOfString<T>(nomeQuery, null);
        }

        /// <summary>
        /// Metodo che restituisce il numero di righe modificate utilizzando la query indicata in <paramref name="nomeQuery"/>
        /// </summary>
        /// <typeparam name="T">Tipo oggetto da cui prenderà la query</typeparam>
        /// <param name="nomeQuery">Nome della query definita nella classe</param>
        /// <param name="parameters">Lista di parametri da passare alla query</param>
        /// <returns></returns>
        public static int ExecuteNonQueryToNRow<T>(string nomeQuery, params Parameter[] parameters)
        {
            Type tipo = typeof(T);
            var listAttriutes = tipo.GetCustomAttributes(typeof(NonQuery), false);
            string query = null;
            if (listAttriutes.Length >= 1)
            {
                foreach (var item in listAttriutes)
                {
                    if (((NonQuery)item).Nome == nomeQuery)
                        query = item.ToString();
                }
                if (string.IsNullOrEmpty(query))
                    return -1;
                using (SqlConnection SqlConn = new SqlConnection(stringConnection))
                {
                    SqlConn.Open();
                    using (SqlCommand SqlCmd = SqlConn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        if (parameters != null)
                        {
                            foreach (var item in parameters)
                            {
                                SqlCmd.Parameters.Add(new SqlParameter(item.Name, item.Value));
                            }
                        }
                        return SqlCmd.ExecuteNonQuery();
                    }
                }
            }
            return -1;
        }

        public static int ExecuteNonQueryToNRow<T>(string nomeQuery)
        {
            return ExecuteNonQueryToNRow<T>(nomeQuery, null);
        }


        #endregion

        #region Insert Region
        /// <summary>
        /// Inserisce l'oggetto <paramref name="input"/> nel database
        /// </summary>
        /// <typeparam name="T">Tipo di oggetto da passare</typeparam>
        /// <param name="input">oggetto da passare</param>
        /// <returns>True se è andato a buon fine</returns>
        public static bool Insert<T>(T input)
            where T:new()
        {
            string query = "";
            StringBuilder str = new StringBuilder();
            List<string> tempValues = new List<string>();
            List<string> tempNames = new List<string>();
            Type tipo = typeof(T);
            var listAttriutes = tipo.GetCustomAttributes(typeof(Nome), false);
            if (listAttriutes.Length >= 1)
            {
                System.Reflection.PropertyInfo[] prop = tipo.GetProperties();
                using (SqlConnection SqlCnn = new SqlConnection(stringConnection))
                {
                    SqlCnn.Open();
                    using (SqlCommand SqlCmd = SqlCnn.CreateCommand())
                    {
                        foreach (var item in prop)
                        {
                            var attrs = item.GetCustomAttributes(typeof(Nome), false);
                            var typeAttr = item.GetCustomAttributes(typeof(TableDefinition), false);
                            if(attrs.Length > 0)
                            {
                                if (typeAttr.Length > 0 && ((TableDefinition)typeAttr[0]).autoIncrement && ((TableDefinition)typeAttr[0]).pK)
                                { }
                                else
                                {
                                    tempNames.Add(string.Format("{0}", attrs[0]));
                                    tempValues.Add(string.Format("@{0}", attrs[0]));
                                    SqlCmd.Parameters.AddWithValue( attrs[0].ToString(), item.GetValue(input, null));
                                }
                            }
                        }
                        query = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", listAttriutes[0], string.Join(",", tempNames), string.Join(",", tempValues));
                        SqlCmd.CommandText = query;
                        if (SqlCmd.ExecuteNonQuery() > 0)
                            return true;
                    }
                }
            }
            return false;
        }

        /// <summary>
        /// Inserisce l'oggetto <paramref name="input"/> nel database e restituisce il suo id sul database
        /// </summary>
        /// <typeparam name="T">Tipo di oggetto da passare</typeparam>
        /// <param name="input">oggetto da passare</param>
        /// <returns>Restituisce l'oggetto in ingresso con id valorizzato</returns>
        public static T InsertWithId<T>(T input)
            where T : new()
        {
            try
            {
                string query = "";
                List<string> tempValues = new List<string>();
                List<string> tempNames = new List<string>();
                StringBuilder str = new StringBuilder();
                System.Reflection.PropertyInfo idProp = null;
                Type tipo = typeof(T);
                var listAttriutes = tipo.GetCustomAttributes(typeof(Nome), false);
                if (listAttriutes.Length >= 1)
                {
                    System.Reflection.PropertyInfo[] prop = tipo.GetProperties();
                    using (SqlConnection SqlCnn = new SqlConnection(stringConnection))
                    {
                        SqlCnn.Open();
                        using (SqlCommand SqlCmd = SqlCnn.CreateCommand())
                        {
                            foreach (var item in prop)
                            {
                                var attrs = item.GetCustomAttributes(typeof(Nome), false);
                                var typeAttr = item.GetCustomAttributes(typeof(TableDefinition), false);
                                if (attrs.Length > 0)
                                {
                                    if (typeAttr.Length > 0 && ((TableDefinition)typeAttr[0]).autoIncrement && ((TableDefinition)typeAttr[0]).pK)
                                    {
                                        idProp = item;
                                    }
                                    else
                                    {
                                        
                                        tempNames.Add(string.Format("{0}", attrs[0]));
                                        tempValues.Add(string.Format("@{0}", attrs[0]));
                                        SqlCmd.Parameters.AddWithValue(attrs[0].ToString(), item.GetValue(input, null));
                                    }
                                }
                            }
                            query = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", listAttriutes[0], string.Join(",", tempNames), string.Join(",", tempValues));
                            SqlCmd.CommandText = query;
                            if (SqlCmd.ExecuteNonQuery() > 0 && idProp != null)
                            {
                                SqlCmd.CommandText = "SELECT @@IDENTITY";
                                idProp.SetValue(input, int.Parse(SqlCmd.ExecuteScalar().ToString()), null);
                                return input;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new InsertDbExceptions("Non è stato possibile inserire l'oggetto nel database" );
            }
            return new T();
        }
        #endregion

        #region Delete Region

        public static bool DeleteFromId<T>(object id)
        {
            
            StringBuilder str = new StringBuilder();
            string tempValues = string.Empty;
            string tempNames = string.Empty;
            
            Type tipo = typeof(T);
            var listAttriutes = tipo.GetCustomAttributes(typeof(Nome), false);
            if (listAttriutes.Length >= 1)
            {
                foreach (var prop in tipo.GetProperties())
                {
                    var typeAttr = prop.GetCustomAttributes(typeof(TableDefinition), false);
                    if (typeAttr.Length > 0 && ((TableDefinition)typeAttr[0]).autoIncrement && ((TableDefinition)typeAttr[0]).pK)
                    {
                        var nameProp = prop.GetCustomAttributes(typeof(Nome), false);
                        if (nameProp.Length > 0)
                        {
                            string query = string.Format("DELETE FROM {0} WHERE {1} = @id", listAttriutes[0], nameProp[0]);
                            using (SqlConnection SqlCnn = new SqlConnection(stringConnection))
                            {
                                SqlCnn.Open();
                                using (SqlCommand SqlCmd = SqlCnn.CreateCommand())
                                {
                                    SqlCmd.CommandText = query;
                                    SqlCmd.Parameters.AddWithValue("id", id);
                                    if (SqlCmd.ExecuteNonQuery() > 0)
                                        return true;
                                }
                            }
                        }
                    }
                }
            }
            return false;
        }

        #endregion

        #region Altri Metodi

        public static void SetProperty<T>(T t, SqlDataReader reader, KeyValuePair<string, System.Reflection.PropertyInfo> prop)
        {
            if (!reader.IsDBNull(reader.GetOrdinal(prop.Key)))
            {
                if (prop.Value.PropertyType.IsEnum)
                {
                    object en = Enum.Parse(prop.Value.PropertyType, reader[prop.Key].ToString());
                    prop.Value.SetValue(t, en, null);
                }
                else
                    prop.Value.SetValue(t, reader[prop.Key], null);
            }
        }
        #endregion

    }
}